package com.bt.springboot.common.util;

import com.bt.springboot.entity.ScrmLeads;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @author zkx
 * @Date 2023/5/4 18:39
 */
@Slf4j
public class ExcelUtil {

	private ExcelUtil() {
	}

	private static ExcelUtil instance = new ExcelUtil();

	public static ExcelUtil getInstance() {
		return instance;
	}

	/**
	 * 将 List<Map<String,Object>> 类型的数据导出为 Excel
	 * 默认 Excel 文件的输出路径为 项目根目录下
	 * 文件名为 filename + 时间戳 + .xlsx
	 *
	 * @param mapList  数据源(通常为数据库查询数据)
	 * @param filename 文件名前缀, 实际文件名后会加上日期
	 * @param title    表格首行标题
	 * @return 文件输出路径
	 */
	public String createExcel(List<Map<String, Object>> mapList, String filename, String title) {
		// 创建工作簿
		XSSFWorkbook wb = new XSSFWorkbook();
		// 创建表格
		XSSFSheet sheet = wb.createSheet();
		// 创建标题行
		Row titleRow = sheet.createRow(0);
		// 标题样式
		CellStyle titleCellStyle = wb.createCellStyle();
		Font titleFont = wb.createFont();
		titleFont.setBold(true);
		titleCellStyle.setFont(titleFont);
		Cell cell = titleRow.createCell(0);
		cell.setCellValue(title);
		cell.setCellStyle(titleCellStyle);
		// 设置行高
		sheet.setDefaultRowHeight((short) (2 * 256));
		// 合并单元格
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, mapList.get(0).size() - 1));
		// 为所有数据设置列宽
		List<String> headList = new ArrayList<>(mapList.get(0).keySet());
		for (int i = 0; i < headList.size(); i++) {
			sheet.setColumnWidth(i, 8000);
		}
		// 设置单元格格式


		// 创建数据行
		int rowIndex = 1;
		for (Map<String, Object> rowData : mapList) {
			Row dataRow = sheet.createRow(rowIndex++);
			int columnIndex = 0;
			for (Map.Entry<String, Object> entry : rowData.entrySet()) {
				Cell dataCell = dataRow.createCell(columnIndex++);
				dataCell.setCellValue(entry.getValue().toString());
			}
		}

		// 输出文件
		String filePath = filename + "_" + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()) + ".xlsx";
		try (FileOutputStream outputStream = new FileOutputStream(filePath)) {
			wb.write(outputStream);
		} catch (IOException e) {
			throw new RuntimeException();
		}
		return filePath;
	}

	public List<ScrmLeads> readLeadsExcel() {
		List<ScrmLeads> scrmLeadsList = new ArrayList<>();
		try {
			//创建工作簿对象
			XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new FileInputStream("C:\\Users\\SuperX\\Desktop\\抖音信息流刷数据.xlsx"));
			//获取工作簿下sheet的个数
			int sheetNum = xssfWorkbook.getNumberOfSheets();
			System.out.println("该excel文件中总共有：" + sheetNum + "个sheet");
			//遍历工作簿中的所有数据
			for (int i = 0; i < sheetNum; i++) {
				//读取第i个工作表
				XSSFSheet sheet = xssfWorkbook.getSheetAt(i);
				//获取最后一行的num，即总行数。此处从0开始
				int maxRow = sheet.getLastRowNum();
				for (int row = 1; row <= maxRow; row++) {
					//获取最后单元格num，即总单元格数 ***注意：此处从1开始计数***
					ScrmLeads scrmLeads = new ScrmLeads();
					CellType cell0Type = sheet.getRow(row).getCell(0).getCellType();
					if (CellType.NUMERIC.equals(cell0Type)) {
						DecimalFormat df = new DecimalFormat("#");
						scrmLeads.setMobile(df.format(sheet.getRow(row).getCell(0).getNumericCellValue()));
					}
					scrmLeads.setOldSubsite(sheet.getRow(row).getCell(1).toString());
					scrmLeadsList.add(scrmLeads);
				}
			}
//			scrmLeadsList.forEach(System.out::println);
		} catch (IOException e) {
			e.printStackTrace();
		}
		return scrmLeadsList;
	}

	public List<ScrmLeads> readDbLeadsExcel() {
		List<ScrmLeads> scrmLeadsList = new ArrayList<>();
		List<ScrmLeads> modifyLeads = this.readLeadsExcel();
		try {
			//创建工作簿对象
			XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new FileInputStream("C:\\Users\\SuperX\\Desktop\\无标题.xlsx"));
			//获取工作簿下sheet的个数
			int sheetNum = xssfWorkbook.getNumberOfSheets();
			System.out.println("该excel文件中总共有：" + sheetNum + "个sheet");
			//遍历工作簿中的所有数据
			for (int i = 0; i < sheetNum; i++) {
				//读取第i个工作表
				XSSFSheet sheet = xssfWorkbook.getSheetAt(i);
				//获取最后一行的num，即总行数。此处从0开始
				int maxRow = sheet.getLastRowNum();
				for (int row = 1; row <= maxRow; row++) {
					//获取最后单元格num，即总单元格数 ***注意：此处从1开始计数***
					ScrmLeads scrmLeads = new ScrmLeads();
					scrmLeads.setId(Long.valueOf(String.valueOf(sheet.getRow(row).getCell(0))));
					scrmLeads.setMobile(sheet.getRow(row).getCell(1).toString());

					ScrmLeads modifylead = modifyLeads.stream().filter(l -> scrmLeads.getMobile().equals(l.getMobile())).findAny().orElse(new ScrmLeads());
					scrmLeads.setOldSubsite(modifylead.getOldSubsite());
					scrmLeadsList.add(scrmLeads);

				}
			}
			scrmLeadsList.forEach(System.out::println);
		} catch (IOException e) {
			e.printStackTrace();
		}
		return scrmLeadsList;
	}


	public List<XSSFRow> readExcel(String filePath) {
		List<XSSFRow> list = new ArrayList<>();
		try {
			//创建工作簿对象
			XSSFWorkbook xssfWorkbook = new XSSFWorkbook(Files.newInputStream(Paths.get(filePath)));
			//获取工作簿下sheet的个数
			int sheetNum = xssfWorkbook.getNumberOfSheets();
			log.info("该excel文件中总共有：" + sheetNum + "个sheet");
			//遍历工作簿中的所有数据
			for (int i = 0; i < sheetNum; i++) {
				//读取第i个工作表
				XSSFSheet sheet = xssfWorkbook.getSheetAt(i);
				//获取最后一行的num，即总行数。此处从0开始
				int maxRow = sheet.getLastRowNum();
				for (int row = 1; row <= maxRow; row++) {
					//获取最后单元格num，即总单元格数 ***注意：此处从1开始计数***
					XSSFRow sheetRow = sheet.getRow(row);

					list.add(sheetRow);
				}
			}
		} catch (IOException e) {
			log.error("", e);
		}
		return list;
	}

	private void writeAccountToTxt(List<XSSFRow> sheetRowList) {
		// 写入文件
		try (// 创建文件输出流
			 FileWriter fw2 = new FileWriter("C:\\Users\\SuperX\\Desktop\\updateAdsAccount.sql");
			 // 使用缓冲区数据流封装输出流
			 BufferedWriter bw = new BufferedWriter(fw2)) {

			//循环写入100行数据
			for (XSSFRow sheetRow : sheetRowList) {
				// 写入数据到输出流
				// 请查看示例 https://gist.github.com/retanoj/5fd369524a18ab68a4fe7ac5e0d121e8
				String insertSql = "insert into ads_account(user_name,account_sn,rebate,supplier,creator_name,ad_merchant_id,frame,project_id,resolve_addr,master_account,transfer_account_group,account_login_name,account_login_password,domain,copyright,is_impulse,is_grant,account_open_time,bind_phone_number,account_open_direct_leader,xl_bindable,is_bind_xl,enabled) values(";
				for (int i = 0; i < 23; i++) {
					insertSql = insertSql + "'" + sheetRow.getCell(i).toString() + "',";
				}
				insertSql = insertSql.substring(0, insertSql.length() - 1);
				insertSql = insertSql + ");";
				bw.write(insertSql);
				// 写入换行符
				bw.newLine();
				// 刷新缓冲区
				bw.flush();
			}

			log.info("成功写入数据！");

		} catch (IOException e) {
			log.error("", e);
		}
	}

	private void writeRechargeToTxt(List<XSSFRow> sheetRowList) {
		// 写入文件
		try (// 创建文件输出流
			 FileWriter fw2 = new FileWriter("C:\\Users\\SuperX\\Desktop\\updateAdsAccountRecharge.sql");
			 // 使用缓冲区数据流封装输出流
			 BufferedWriter bw = new BufferedWriter(fw2)) {

			log.info("sheetRowList size:{}", sheetRowList.size());
			//循环写入100行数据
			for (XSSFRow sheetRow : sheetRowList) {
				String userName = sheetRow.getCell(0).toString();
				CellType cell0Type = sheetRow.getCell(0).getCellType();
				DecimalFormat df = new DecimalFormat("#");
				if (CellType.NUMERIC.equals(cell0Type)) {
					userName = df.format(sheetRow.getCell(0).getNumericCellValue());
				}

				// 写入数据到输出流
				String insertSql = "insert into ads_account_recharge_record(ads_account_id,recharge_amount,recharge_rebate,recharge_electron_amount,recharger,recharge_status,apply_time,arrival_time) values(";
				for (int i = 1; i < 8; i++) {
					XSSFCell cell = sheetRow.getCell(i);
					String s = "";
					if (cell == null) {
						insertSql = insertSql + "'" + s + "',";
						continue;
					} else {
						s = cell.toString();
					}
					CellType cellType = cell.getCellType();
					if (CellType.NUMERIC.equals(cellType)) {
						if (DateUtil.isCellDateFormatted(cell)){
							SimpleDateFormat myFmt=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
							s = myFmt.format(cell.getDateCellValue());
						}else {
							s = df.format(sheetRow.getCell(i).getNumericCellValue());
						}

					}

					insertSql = insertSql + "'" + s + "',";
				}
				insertSql = insertSql.substring(0, insertSql.length() - 1);
				insertSql = insertSql + ");";
				bw.write(insertSql);
				// 写入换行符
				bw.newLine();
				// 刷新缓冲区
				bw.flush();
			}

			log.info("成功写入数据！");

		} catch (IOException e) {
			log.error("", e);
		}
	}


	public static void main(String[] args) {
		ExcelUtil instance = ExcelUtil.getInstance();
//
//		List<Map<String, Object>> list = new ArrayList<>();
//		for (int i = 0; i < 3; i++) {
//			Map<String, Object> map = new HashMap<>();
//			map.put("测试1", i);
//			map.put("测试2", i);
//			map.put("测试3", i);
//			list.add(map);
//		}
//
//		instance.createExcel(list,"t","hh");
//		List<ScrmLeads> scrmLeadsList = instance.readDbLeadsExcel();
//
//		// 写入文件
//		try (// 创建文件输出流
//			 FileWriter fw2 = new FileWriter("C:\\Users\\SuperX\\Desktop\\updateLeads2.sql");
//			 // 使用缓冲区数据流封装输出流
//			 BufferedWriter bw = new BufferedWriter(fw2)) {
//
//			//循环写入100行数据
//			for (ScrmLeads scrmLeads : scrmLeadsList) {
//				// 写入数据到输出流
//				// 请查看示例 https://gist.github.com/retanoj/5fd369524a18ab68a4fe7ac5e0d121e8
//				bw.write("update scrm_leads set old_subsite = '" + scrmLeads.getOldSubsite() +  "' where id = " + scrmLeads.getId() + ";");
//				// 写入换行符
//				bw.newLine();
//				// 刷新缓冲区
//				bw.flush();
//			}
//
//			log.info("成功写入数据！");
//
//		} catch (IOException e) {
//			log.error("", e);
//		}
		List<XSSFRow> xssfRows = instance.readExcel("C:\\Users\\SuperX\\Desktop\\框内-_20230601_20231219.xlsx");
		instance.writeRechargeToTxt(xssfRows);

	}
}
